package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import model.CareerDev;
import model.Employee;
import model.JobRes;
import model.Project;
import model.Title;

public class ProfileDAO {
	ConnectionDB con;
	ObjectDAO objectDao;
	EmployeeDAO emDao;
	SimpleDateFormat format; 
	
	public ProfileDAO() throws SQLException, ClassNotFoundException{
		con = new ConnectionDB();
		
		objectDao = new ObjectDAO();
		emDao = new EmployeeDAO();
		
		format = new SimpleDateFormat("yyyy-MM-dd");
	}
	
	
	
	@SuppressWarnings("static-access")
	public Employee getProjectDirector(int projectId) throws SQLException {
		con = new ConnectionDB();
		Employee em = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Employee WHERE titleId = 6 AND projectId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, projectId);
            rs = ps.executeQuery();
            

            if (rs.next()) {
            	em = emDao.getAEmployee(rs.getInt(1));
            }
			rs.close();
			ps.close();
			return em;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return em;
		
	}
	
	
	
	/**
	 * remove a career development
	 * @param caDevId
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean  removeCareerDev(int caDevId) throws SQLException {
		con = new ConnectionDB();
		PreparedStatement ps;
		try {
			String query = "DELETE CareerDev WHERE CareerDevId=?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, caDevId);

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();
            	ps.close();
            	return true;
            }
            else {
            	ps.close();
            	return false;
            }
            	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	
	/**
	 * edit new career development
	 * @param caDev
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean editCareerDev(CareerDev caDev) throws SQLException {
		con = new ConnectionDB();
		PreparedStatement ps;
		System.out.println(format.format(caDev.getTarget()));
		try {
			String query = "UPDATE CareerDev SET Goal_objective=?, actionStep=?, targetDate=?, completeDate=?, comment=?," +
					" actionStatus=?, userid=? WHERE careerDevId=?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setString(1, caDev.getGoal());
            ps.setString(2, caDev.getAction());
            ps.setString(3, format.format(caDev.getTarget()));
            ps.setString(4, format.format(caDev.getComplete()));
            ps.setString(5, caDev.getComment());
            ps.setString(6, caDev.getStatus());
            ps.setInt(7, caDev.getEmployee().getUserid());
            ps.setInt(8, caDev.getCareerDevId());

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();
            	ps.close();
            	return true;
            }
            else {
            	ps.close();
            	return false;
            }
            
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	/**
	 * add new career development
	 * @param caDev
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean addCareerDev(CareerDev caDev) throws SQLException {
		con = new ConnectionDB();
		PreparedStatement ps;
		System.out.println(format.format(caDev.getTarget()));
		try {
			String query = "INSERT INTO CareerDev VALUES (?, ?, ?, ?, ?, ?, ?)";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setString(1, caDev.getGoal());
            ps.setString(2, caDev.getAction());
            ps.setString(3, format.format(caDev.getTarget()));
            ps.setString(4, format.format(caDev.getComplete()));
            ps.setString(5, caDev.getComment());
            ps.setString(6, caDev.getStatus());
            ps.setInt(7, caDev.getEmployee().getUserid());

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();
            	ps.close();
            	return true;
            }
            else {
            	ps.close();
            	return false;
            }
            	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	/**
	 * get a specific career development
	 * @param caDevId
	 * @return CareerDev
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public CareerDev getACareerDev(int caDevId) throws SQLException {
		con = new ConnectionDB();
		CareerDev caDev = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM CareerDev WHERE CareerDevId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, caDevId);
            rs = ps.executeQuery();
            

            if (rs.next()) {
            	Employee employee = emDao.getAEmployee(rs.getInt(8));
            	caDev = new CareerDev(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4),
                		rs.getDate(5), rs.getString(6), rs.getString(7), employee);
            }
			rs.close();
			ps.close();
            
			return caDev;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return caDev;
	}
	
	
	/**
	 * get all career Developments of a employee from db 
	 * @return list of JobRes
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public ArrayList<CareerDev> listCareerDevOfAEmployee(int userId) throws SQLException {
		con = new ConnectionDB();
		ArrayList<CareerDev> list = new ArrayList<CareerDev>();
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM CareerDev WHERE userid = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, userId);
            rs = ps.executeQuery();
            
            while (rs.next()) {                
                Employee employee = emDao.getAEmployee(rs.getInt(8));
                CareerDev caDev = new CareerDev(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4),
                		rs.getDate(5), rs.getString(6), rs.getString(7), employee);
                
            	list.add(caDev);
            }
			rs.close();
			ps.close();
            
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		
		return list;
	}
	
	
	
	/**
	 * get all job responsibilities from db 
	 * @return list of JobRes
	 * @throws SQLException
	 */
	public ArrayList<CareerDev> listCareerDev() throws SQLException {
		con = new ConnectionDB();
		ArrayList<CareerDev> list = new ArrayList<CareerDev>();
		Statement st;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM CareerDev";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {                
                Employee employee = emDao.getAEmployee(rs.getInt(8));
                CareerDev caDev = new CareerDev(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4),
                		rs.getDate(5), rs.getString(6), rs.getString(7), employee);
                
            	list.add(caDev);
            }
            rs.close();
            st.close();
			
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		
		return list;
	}
	
	
	//===================================================================================================================//
	
	
	/**
	 * remove a job responsibility
	 * @param jobId
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean  removeJobRes(int jobId) throws SQLException {
		con = new ConnectionDB();
		PreparedStatement ps;
		try {
			String query = "DELETE JobRes WHERE jobResId=?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, jobId);

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();
            	ps.close();
            	return true;
            }
            else  {
            	ps.close();
            	return false;
            }
            	
            
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	
	/**
	 * edit job responsibility
	 * @param job
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean editJobRes(JobRes job) throws SQLException {
		con = new ConnectionDB();
		PreparedStatement ps;
		System.out.println(format.format(job.getTarget()));
		try {
			String query = "UPDATE JobRes SET Goal_objective=?, actionStep=?, targetDate=?, completeDate=?, comment=?," +
					" actionStatus=?, userid=? WHERE jobResId=?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setString(1, job.getGoal());
            ps.setString(2, job.getAction());
            ps.setString(3, format.format(job.getTarget()));
            ps.setString(4, format.format(job.getComplete()));
            ps.setString(5, job.getComment());
            ps.setString(6, job.getStatus());
            ps.setInt(7, job.getEmployee().getUserid());
            ps.setInt(8, job.getJobResId());

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();
    			ps.close();
            	return true;
            }
            else {
            	ps.close();
            	return false;
            }
            	
            
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	/**
	 * add new Job responsibility
	 * @param job
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean addJobRes(JobRes job) throws SQLException {
		con = new ConnectionDB();
		PreparedStatement ps;
		System.out.println(format.format(job.getTarget()));
		try {
			String query = "INSERT INTO JobRes VALUES (?, ?, ?, ?, ?, ?, ?)";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setString(1, job.getGoal());
            ps.setString(2, job.getAction());
            ps.setString(3, format.format(job.getTarget()));
            ps.setString(4, format.format(job.getComplete()));
            ps.setString(5, job.getComment());
            ps.setString(6, job.getStatus());
            ps.setInt(7, job.getEmployee().getUserid());

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();

    			ps.close();
            	return true;
            }
            else {
    			ps.close();
            	return false;
            }
            
            
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	/**
	 * get a specific Job responsibility
	 * @param jobId
	 * @return JobRes
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public JobRes getAJobRes(int jobId) throws SQLException {
		con = new ConnectionDB();
		JobRes job = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM JobRes WHERE JobResId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, jobId);
            rs = ps.executeQuery();
            

            if (rs.next()) {
            	Employee employee = emDao.getAEmployee(rs.getInt(8));
                job = new JobRes(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4),
                		rs.getDate(5), rs.getString(6), rs.getString(7), employee);
            }
			

			rs.close();
			ps.close();
			return job;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return job;
	}
	
	
	/**
	 * get all job responsibilities of a employee from db 
	 * @return list of JobRes
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public ArrayList<JobRes> listJobResOfAEmployee(int userId) throws SQLException {
		con = new ConnectionDB();
		ArrayList<JobRes> list = new ArrayList<JobRes>();
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM JobRes WHERE userid = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, userId);
            rs = ps.executeQuery();
            
            while (rs.next()) {                
                Employee employee = emDao.getAEmployee(rs.getInt(8));
                JobRes job = new JobRes(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4),
                		rs.getDate(5), rs.getString(6), rs.getString(7), employee);
                
            	list.add(job);
            }
			

			rs.close();
			ps.close();
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		
		return list;
	}
	
	
	/**
	 * get all job responsibilities from db 
	 * @return list of JobRes
	 * @throws SQLException
	 */
	public ArrayList<JobRes> listJobRes() throws SQLException {
		con = new ConnectionDB();
		ArrayList<JobRes> list = new ArrayList<JobRes>();
		Statement st;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM JobRes";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {                
                Employee employee = emDao.getAEmployee(rs.getInt(8));
                JobRes job = new JobRes(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4),
                		rs.getDate(5), rs.getString(6), rs.getString(7), employee);
                
            	list.add(job);
            }
            

			rs.close();
			st.close();
			
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		
		return list;
	}
}
